package cn.util;

import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

public class DBUtil {
	// 1、数据库连接参数
	private static String driver;
	private static String url;
	private static String userName;
	private static String passWord;

	// 2、加载驱动，获得连接
	static {
		Properties p = new Properties();
		// 打开流
		InputStream in = DBUtil.class.getClassLoader().getResourceAsStream("jdbc.properties");
		try {
			p.load(in);
			driver = p.getProperty("driver");
			url = p.getProperty("url");
			userName = p.getProperty("userName");
			passWord = p.getProperty("passWord");
			// 加载驱动
			Class.forName(driver);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 3、获得连接
	public Connection getConnection() {
		try {
			Connection ct = DriverManager.getConnection(url, userName, passWord);
			// 开启事务管理
			ct.setAutoCommit(false);
			// 设置事务隔离级别
			ct.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
			return ct;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public static void main(String[] args) {
		DBUtil u = new DBUtil();
		Connection ct = u.getConnection();
		System.out.println(ct);
	}

	// 3、释放资源
	public void release(Connection ct, Statement ps, ResultSet st) {
		if (st != null) {
			try {
				st.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (ps != null) {
			try {
				ps.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if (ct != null) {
			try {
				ct.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	// 通用的增删改方法:cud
	public <T> Integer[] cud(Class<T> clazz, String sql, Object... args) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		Integer[] num = new Integer[2];
		System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			for (int i = 0; i < args.length; i++) {
				ps.setObject((i + 1), args[i]);
			}
			// 几条受影响
			int rowNum = ps.executeUpdate();
			// 获得自增长键
			st = ps.getGeneratedKeys();
			int k = -1;
			if (st.next()) {
				k = st.getInt(1);
			}
			num[0] = rowNum;
			num[1] = k;
			ct.commit();
		} catch (Exception e) {
			e.printStackTrace();
			try {
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			release(ct, ps, st);
		}
		return num;
	}

	// 通用的查询方法
	public <T> List<T> getT(Class<T> clazz, String sql, List<Object> params) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		// System.out.println(sql);
		try {
			ps = ct.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.size(); i++) {
					ps.setObject((i + 1), params.get(i));
				}
			}
			st = ps.executeQuery();
			ResultSetMetaData rt = st.getMetaData();
			T t = null;
			Method[] mt = clazz.getDeclaredMethods();
			List<T> list = new ArrayList<T>();
			while (st.next()) {
				t = clazz.newInstance();
				for (int i = 0; i < rt.getColumnCount(); i++) {
					String label = rt.getColumnLabel(i + 1);
					label = "set" + (label.charAt(0) + "").toUpperCase() + label.substring(1);
					for (int j = 0; j < mt.length; j++) {
						if (mt[j].getName().equals(label)) {
							mt[j].invoke(t, st.getObject(i + 1));
							break;
						}
					}
				}
				list.add(t);
			}
			return list;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 2、通用的查询方法：1条sql语句，多个参数
	public <T> List<T> get(Class<T> clazz, String sql, Object... args) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		List<T> list = new ArrayList<T>();
		try {
			T t = clazz.newInstance();
			Method mt[] = clazz.getDeclaredMethods();
			ps = ct.prepareStatement(sql);
			if (args.length > 0) {
				for (int i = 0; i < args.length; i++) {
					ps.setObject((i + 1), args[i]);
				}
			}
			st = ps.executeQuery();
			// 获得结果集的元数据
			ResultSetMetaData meta = st.getMetaData();
			// 获字段数量
			int num = meta.getColumnCount();
			while (st.next()) {
				for (int i = 0; i < num; i++) {
					// 获得第一个字段的名称
					String columnLable = meta.getColumnLabel(i + 1);
					// 获得第一个字段的值
					Object value = st.getObject(i + 1);
					// 遍历当前类的所有方法
					for (int k = 0; k < mt.length; k++) {
						Method md = mt[k];
						// 获得当前方法的名称
						String methodName = md.getName();
						// 如果是setter方法
						if (methodName.equals(convertSet(columnLable))) {
							// 获得当前setter方法的参数类型
							String paramType = md.getParameterTypes()[0].getSimpleName();
							// 调用当前对象t的set方法设置制定参数值value
							adpater(mt[k], t, value, paramType);
							break;
						}
					}
				}
				// 把当前对象放入到集合中
				list.add(t);
				return list;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 3、 构造setter方法：name--->setName
	public String convertSet(String name) {
		return name == null ? null : "set" + (name.charAt(0) + "").toUpperCase() + name.substring(1);
	}

	// 4、方法适配器，参数类型可以通过else-if继续进行增加
	public <T> void adpater(Method mt, T t, Object value, String paramType) throws Exception {
		if (paramType.equals("String")) {
			String v = (String) value;
			mt.invoke(t, v);
		} else if (paramType.equals("Integer")) {
			Integer v = (Integer) value;
			mt.invoke(t, v);
		} else if (paramType.equals("Double")) {
			Double v = (Double) value;
			mt.invoke(t, v);
		} else if (paramType.equals("Long")) {
			Long v = (Long) value;
			mt.invoke(t, v);
		} else if (paramType.equals("Date")) {
			Date v = (Date) value;
			mt.invoke(t, v);
		} else if (paramType.equals("Timestamp")) {
			Timestamp v = (Timestamp) value;
			mt.invoke(t, v);
		}
	}

	// 7、数据库通用的增删改操作方法
	public Map<String, Integer> cud1(String sql, Object... args) {
		// 存放几行受影响和自增id的map集合
		Map<String, Integer> map = new HashMap<String, Integer>();
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		// 自增长的id
		Integer generateKey = null;
		// 几行受影响
		Integer affectRows = null;
		try {
			ps = ct.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
			if (args.length > 0) {
				for (int i = 0; i < args.length; i++) {
					ps.setObject((i + 1), args[i]);
				}
			}
			// 获得几行受影响
			affectRows = ps.executeUpdate();
			// 获得自增长id
			st = ps.getGeneratedKeys();
			if (st.next()) {
				generateKey = st.getInt(1);
			}
			map.put("affectRows", affectRows);
			map.put("generateKey", generateKey);
			ct.commit();
			return map;
		} catch (Exception e) {
			e.printStackTrace();
			try {
				ct.rollback();
			} catch (Exception e1) {
				e1.printStackTrace();
			}
		} finally {
			release(ct, ps, st);
		}
		return null;
	}

	// 6、通用的查询总数的方法
	public int getSum(String sql, Object... args) {
		Connection ct = getConnection();
		PreparedStatement ps = null;
		ResultSet st = null;
		try {
			ps = ct.prepareStatement(sql);
			if (args.length > 0) {
				for (int i = 0; i < args.length; i++) {
					ps.setObject((i + 1), args[i]);
				}
			}
			st = ps.executeQuery();
			if (st.next()) {
				return st.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			release(ct, ps, st);
		}
		return 0;
	}
}
